package edu.uic.ids517.model;

import java.io.IOException;
import java.io.Serializable;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

public class DatabaseAccessBean implements Serializable{

	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	private DbLoginBean dbmsUserBean;
	private CreateTableBean createBean;
	private DropTableBean dropTableBean;
	private DisplayTableBean displayBean;
	private SignUpBean signupBean;
	private UserLoginBean userLoginBean;

	private String userName;
	private String password;
	private String url;
	private boolean status = false;

	// table and view list retrieval
	private static final String [] TABLE_TYPES={"TABLE","VIEW"};

	// Driver related data
	private String jdbcDriver;
	private Connection connection;
	private Statement statement;
	private ResultSet resultSet, rs , qResultSet;
	private DatabaseMetaData databaseMetaData;

	/*******GETTERS & SETTERS *******/
	public DatabaseMetaData getDatabaseMetaData() {
		return databaseMetaData;
	}

	public UserLoginBean getUserLoginBean() {
		return userLoginBean;
	}

	public void setUserLoginBean(UserLoginBean userLoginBean) {
		this.userLoginBean = userLoginBean;
	}

	public SignUpBean getSignupBean() {
		return signupBean;
	}

	public void setSignupBean(SignUpBean signupBean) {
		this.signupBean = signupBean;
	}

	public DisplayTableBean getDisplayBean() {
		return displayBean;
	}

	public void setDisplayBean(DisplayTableBean displayBean) {
		this.displayBean = displayBean;
	}

	public DropTableBean getDropTableBean() {
		return dropTableBean;
	}

	public void setDropTableBean(DropTableBean dropTableBean) {
		this.dropTableBean = dropTableBean;
	}

	public CreateTableBean getCreateBean() {
		return createBean;
	}

	public void setCreateBean(CreateTableBean createBean) {
		this.createBean = createBean;
	}

	public void setDatabaseMetaData(DatabaseMetaData databaseMetaData) {
		this.databaseMetaData = databaseMetaData;
	}

	public DbLoginBean getDbmsUserBean() {
		return dbmsUserBean;
	}

	public void setDbmsUserBean(DbLoginBean dbmsUserBean) {
		this.dbmsUserBean = dbmsUserBean;
	}

	/**** CREATING THE CONNECTION****/

	public boolean openConnection() {

		userName= dbmsUserBean.getUserName();
		password = dbmsUserBean.getPassword();

		dbmsUserBean.getDbms().toLowerCase();
		jdbcDriver = "com.mysql.jdbc.Driver";
		url = "jdbc:mysql://" +  dbmsUserBean.getDbmsHost() +  ":3306/" +
				dbmsUserBean.getDatabaseSchema();

		try {

			Class.forName(jdbcDriver).newInstance();
			connection= DriverManager.getConnection(url, userName, password);
			statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
			databaseMetaData = connection.getMetaData();
			status=true;

		}catch (ClassNotFoundException e) {
			System.out.println("Exception Inside Catch 2 : ClassNotFoundException e");
			//e.printStackTrace();
			status= false;
		}catch(SQLException e) {
			System.out.println("Exception Inside Catch 2" + e.getMessage());
			//e.printStackTrace();
			status= false;
		}catch(Exception e) {
			System.out.println("Inside Catch 3 : Exception e");
			//e.printStackTrace();
			status= false;
		}
		return status;
	} 


	/****CLOSING ALL THE CONNECTIONS****/
	public void closeConnection()
	{		
		try {
			if(rs !=null)
				rs.close();

			if(resultSet !=null)
				rs.close();

			if(qResultSet !=null)
				rs.close();

			if(statement != null)
				statement.close();

			if(statement != null)
				connection.close();

		}
		catch (SQLException e) {
			e.printStackTrace();
		}				
	} 

	/****EXECUTE CREATE SQL QUERIES*****/
	public int executeCreateSQL() {

		openConnection();

		int result = 0;
		String createQuery = "";
		String tableName= createBean.getTableName();
		String column1 = createBean.getColumn1();
		String column2 = createBean.getColumn2();
		String column3 = createBean.getColumn3();

		String dataType1 = createBean.getDataType1();
		String dataType2 = createBean.getDataType2();
		String dataType3 = createBean.getDataType3();

		try{
			createQuery = "CREATE TABLE" +" "+ tableName+"("+column1+" "+dataType1+", "+column2+" "+dataType2+", "+column3+" "+dataType3+")";
			System.out.println("createQuery:     "+createQuery);
			statement = connection.createStatement();
			result = statement.executeUpdate(createQuery);
		} catch (SQLException e) {
			System.out.println("Exception caught for executeCreateSQL()");
			e.printStackTrace();			
		}

		closeConnection();
		return result;
	}


	/****EXECUTE DROP SQL QUERIES*****/
	public int executeDropSQL() {

		openConnection();

		int result = 0;
		String dropQuery = "";
		String tableName= dropTableBean.getTableName();

		try{
			dropQuery = "DROP TABLE" +" "+ tableName+"";
			statement = connection.createStatement();
			result = statement.executeUpdate(dropQuery);
		} catch (SQLException e) {
			System.out.println("Exception caught for executeDropSQL()");
			e.printStackTrace();			
		}

		closeConnection();
		return result;
	}

	/****EXECUTE DISPLAY SQL QUERIES*****/
	public ResultSet executeDisplaySQL() {

		openConnection();

		String displayQuery = "";
		String tableName= displayBean.getTableName();

		try{
			displayQuery = "SELECT * FROM" +" "+ tableName+"";
			System.out.println("displayQuery:     "+displayQuery);
			statement = connection.createStatement();
			qResultSet = statement.executeQuery(displayQuery);
		} catch (SQLException e) {
			System.out.println("Exception caught for executeDisplaySQL()");
			e.printStackTrace();			
		} 

		return qResultSet;
	}


	/*******GENERATE LIST OF TABLES*********/
	public List<String> tableList()
	{
		openConnection();
		List <String> tableList = new ArrayList<String>();

		try{
			rs = databaseMetaData.getTables(null, userName, null,
					TABLE_TYPES);

			String tableName="";
			if(rs != null) {
				while(rs.next())
				{
					tableName = rs.getString("TABLE_NAME");

					if(dbmsUserBean.getDbms().equalsIgnoreCase("MySql"))
					{
						tableList.add(tableName);
					}
				}
			}
		}catch(Exception e) {
			e.printStackTrace();				
		}
		closeConnection();
		return tableList;
	} 

	/***********CONVERST RESULTSET TO HTML*******************/
	public String getHtmlTable() {

		openConnection();

		StringBuffer htmlRows = new StringBuffer();
		try{
			ResultSetMetaData resultSetMetaData = resultSet.getMetaData();	
			int columnCount = resultSetMetaData.getColumnCount();

			htmlRows.append("<table><TBODY>");
			htmlRows.append("<tr>");
			for (int i = 0; i <= columnCount; i++)
			{
				htmlRows.append("<td><b>" +  resultSetMetaData.getColumnName(i) + "</td>");
				htmlRows.append("</tr>");
			}
			resultSet.afterLast();
			int rowCount = resultSet.getRow();
			resultSet.beforeFirst();
			while (resultSet.next()){
				htmlRows.append("<tr>");
				for (int i = 0; i <= rowCount; i++)
				{
					htmlRows.append("<td>" +resultSet.getString(i) + "</td>");
				}
				htmlRows.append("</tr>");
			}

			htmlRows.append("</TBODY></TABLE>");
		} catch(Exception e) {
			e.printStackTrace();
			System.out.println("Exception in creating List of HTML TABLE");
		}

		closeConnection();
		return htmlRows.toString();
	}

	public StringBuffer write(ResultSet rs) throws IOException, SQLException {

		openConnection();

		StringBuffer htmlRows = new StringBuffer();
		ResultSetMetaData md = rs.getMetaData();
		int count = md.getColumnCount();
		htmlRows.append("<table border=1>");
		System.out.print("<tr>");
		for (int i=1; i<=count; i++) {
			htmlRows.append("<th>");
			htmlRows.append(md.getColumnLabel(i));
		}
		htmlRows.append("</tr>");
		while (rs.next()) {
			htmlRows.append("<tr>");
			for (int i=1; i<=count; i++) {
				htmlRows.append("<td>");
				htmlRows.append(rs.getString(i));
			}
			htmlRows.append("</tr>");
		}
		htmlRows.append("</table>");

		closeConnection();
		return htmlRows;
	}


	/***********SIGN UP CREATION *******************/
	public int signUpCreation(){

		openConnection();

		int result = 0;
		String insertQuery = "";
		String fname= signupBean.getFirstName();
		String lname = signupBean.getLastName();
		String password = signupBean.getPassword();
		String username = signupBean.getUsername();
		String uin = signupBean.getUin();

		try{
			insertQuery = "INSERT INTO loginTable(userName, password, uin, firstName, lastName, type)"+
					" "+"VALUES('"+username+"' , '"+password+"' , '"+uin+"' , '"+fname+"' , '"+lname+"' , 'general')";
			System.out.println("createQuery:     "+insertQuery);
			statement = connection.createStatement();
			result = statement.executeUpdate(insertQuery);
		} catch (SQLException e) {
			System.out.println("Exception caught for new user signup");
			e.printStackTrace();			
		}

		closeConnection();
		return result;
	}

	public String validateUser(){

		openConnection();

		String userType = "invalid";
		String loginQuery;
		String tableName = "logintable";
		String password = userLoginBean.getPassword();
		String username = userLoginBean.getUserName();
		ResultSet rs = null; 

		try{
			loginQuery = "SELECT * FROM" +" "+ tableName+"";
			//System.out.println("displayQuery:     "+loginQuery);
			statement = connection.createStatement();
			rs = statement.executeQuery(loginQuery);
			//System.out.println("loginQuery : "+rs);

			while(rs.next()){
				//System.out.println(rs.getString(2) + " " + rs.getString(3) + " " + rs.getString(4));
				if(username.equalsIgnoreCase(rs.getString(2)) && password.equals(rs.getString(3))){
					userType = rs.getString(4);
				}				
			}

		} catch (SQLException e) {
			e.printStackTrace();			
		}
		try {
			if(rs !=null){
				rs.close();
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		closeConnection();
		return userType;		
	}

	public void insertTimestamp(String userName){

		openConnection();

		String logQuery = "";
		java.util.Date date= new java.util.Date();		
		Timestamp timestamp = new java.sql.Timestamp(date.getTime());

		try{
			logQuery = "INSERT INTO LOGTABLE (USERNAME,LOGTIME) VALUES ('" + userName + "','" + timestamp + "');";
			System.out.println("logQuery:     "+logQuery);
			statement = connection.createStatement();
			statement.executeUpdate(logQuery);

		} catch (SQLException e) {
			e.printStackTrace();			
		} 
		closeConnection();
	}

}

